﻿//************************************************************************************************************
//文件名称(File Name)：ChartBuildMana.cs
//功能描述(Description)：生成图表的管理类
//数据表(Tables)：
//作者(Author)：
//日期(Create Date)：20130302
//参考文档(Reference)(可选)：
//引用(Using) (可选)﹕
//************************************************************************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace Water.Model
{
    public class ChartBuildMana
    {
        public string username
        {
            get;
            set;
        }
        public string chartno
        {
            get;
            set;
        }

        public string charttitle
        {
            get;
            set;
        }

        public string categories
        {
            get;
            set;
        }

        public string seriesname
        {
            get;
            set;
        }

        public string seriesdata
        {
            get;
            set;
        }

        public string secseriesdata
        {
            get;
            set;
        }

        public ChartBuildMana getChartBuild(string chartno, string uname, string selvalue = "1")
        {
            ChartTitle chartTi = new ChartTitle().getChartTitle(chartno);
            ChartSeries chartser = new ChartSeries().getChartSeries(chartno, selvalue, uname);
            ChartBuildMana chartbm = new ChartBuildMana();
            chartbm.username = chartser.username;
            chartbm.chartno = chartTi.chartno;
            chartbm.charttitle = chartTi.charttitle;
            chartbm.categories = "[" + chartTi.xaxis + "]";
            chartbm.seriesname = chartser.seriesname;
            chartbm.seriesdata = chartser.seriesdata;
            chartbm.secseriesdata = chartTi.xaxis;  //0320 update 
            return chartbm;
        }

        /// <summary>
        /// add by jy
        /// </summary>
        /// <param name="chartno"></param>
        /// <param name="uname"></param>
        /// <param name="selvalue"></param>
        /// <returns></returns>
        public ChartBuildMana getChartBuildNew(string chartno, string uname, string selvalue = "1")
        {
            ChartTitle chartTi = new ChartTitle().getChartTitle(chartno);
            ChartSeries chartser = new ChartSeries().getChartSeriesNew(chartno);
            ChartBuildMana chartbm = new ChartBuildMana();
            chartbm.username = chartser.username;
            chartbm.chartno = chartTi.chartno;
            chartbm.charttitle = chartTi.charttitle;
            chartbm.categories = "[" + chartTi.xaxis + "]";
            chartbm.seriesname = chartser.seriesname;
            chartbm.seriesdata = chartser.seriesdata;
            chartbm.secseriesdata = chartTi.xaxis;  //0320 update 
            return chartbm;
        }

        public ChartBuildMana getSecChartBuild(string chartno, string uname, string selvalue = "1")
        {
            ChartTitle chartTi = new ChartTitle().getChartTitle(chartno);
            ChartSeries chartser = new ChartSeries().getsecChartSeries(chartno, selvalue, uname);
            ChartBuildMana chartbm = new ChartBuildMana();
            chartbm.username = chartser.username;
            chartbm.chartno = chartTi.chartno;
            chartbm.charttitle = chartTi.charttitle;
            chartbm.categories = "[" + chartTi.xaxis + "]";
            chartbm.seriesname = chartser.seriesname;
            chartbm.seriesdata = chartser.seriesdata;
            chartbm.secseriesdata = chartser.secseriesdata;
            return chartbm;
        }

        /// <summary>
        /// 通过查询条件生成图表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public ChartBuildMana getChartBuildBySql(string condition)
        {
            string sql = "select A.USERNAME,B.CHARTNO,B.CHARTTITLE,B.XAXIS,A.SERIESNAME,A.SERIESDATA "
                       + "from Tbt_ChartSeries A LEFT JOIN Tbt_ChartTitle B ON A.CHARTNO = B.CHARTNO" + condition;
            sqldb db = new sqldb();
            SqlDataReader sdr = db.ExecuteReader(sqlconn.GetConnStr(), CommandType.Text, sql);
            if (sdr.HasRows)
            {
                sdr.Read();
                ChartBuildMana chartbm = new ChartBuildMana();
                chartbm.username = sdr.GetString(0);
                chartbm.chartno = sdr.GetString(1);
                chartbm.charttitle = sdr.GetString(2);
                chartbm.categories = "[" + sdr.GetString(3) + "]";
                chartbm.seriesname = sdr.GetString(4);
                chartbm.seriesdata = sdr.GetString(5);
                chartbm.secseriesdata = sdr.GetString(3);  //0320 update
                return chartbm;
            }
            else
            {
                return null;
            }
        }
    }

    /// <summary>
    /// 图表的标题
    /// </summary>
    public class ChartTitle
    {
        public string chartno
        {
            get;
            set;
        }

        public string charttitle
        {
            get;
            set;
        }

        public string xaxis
        {
            get;
            set;
        }

        public ChartTitle getChartTitle(string chartno)
        {
            string sql = "select CHARTNO,CHARTTITLE,XAXIS from Tbt_ChartTitle where CHARTNO = '"
                        + chartno + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            ChartTitle chartt = new ChartTitle();
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                chartt.chartno = dr["CHARTNO"].ToString();
                chartt.charttitle = dr["CHARTTITLE"].ToString();
                string[] arrxaxis = dr["XAXIS"].ToString().Split(',');
                foreach (string perxaxis in arrxaxis)
                {
                    string pperxaxis = "'" + perxaxis + "',";
                    chartt.xaxis += pperxaxis;
                }
                chartt.xaxis = chartt.xaxis.Substring(0, chartt.xaxis.Length - 1);
            }
            return chartt;
        }
    }

    /// <summary>
    /// 图表值
    /// </summary>
    public class ChartSeries
    {
        public string username
        {
            get;
            set;
        }
        public string seriesname
        {
            get;
            set;
        }

        public string seriesdata
        {
            get;
            set;
        }

        public string secseriesdata
        {
            get;
            set;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="chartno"></param>
        /// <returns></returns>
        public ChartSeries getChartSeries(string chartno, string selvalue, string uname)
        {
            string sql = "select USERNAME,SERIESNAME,SERIESDATA from Tbt_ChartSeries "
                + "where CHARTNO = '" + chartno + "' and SELECTVALUE = '" + selvalue + "' and USERNAME = '" + uname + "'";
            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            ChartSeries chartser = new ChartSeries();
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                chartser.username = dr["USERNAME"].ToString();
                chartser.seriesname = dr["SERIESNAME"].ToString();
                chartser.seriesdata = dr["SERIESDATA"].ToString();
            }
            return chartser;
        }


        /// <summary>
        ///  
        /// </summary>
        /// <param name="chartno"></param>
        /// <returns></returns>
        public ChartSeries getChartSeriesNew(string chartno)
        {
            if ((chartno == "F1016") || (chartno == "F1017") || (chartno == "F1018") || (chartno == "F1022"))
            {
                string sql = "select USERNAME,SERIESNAME,SERIESDATA from Tbt_ChartSeries "
    + "where CHARTNO = '" + chartno + "' ";
                sqldb db = new sqldb();
                DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
                ChartSeries chartser = new ChartSeries();
                chartser.username = "admin";
                chartser.seriesname = "test";
                string strSerial = "";
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    strSerial = "[";
                    for (int iRow = 0; iRow < ds.Tables[0].Rows.Count; iRow++)
                    {
                        strSerial += "{ name:";
                        DataRow dr = ds.Tables[0].Rows[iRow];
                        strSerial += "'" + dr["SERIESNAME"].ToString() + "'";
                        if (iRow == 0)
                        {
                            strSerial += ",type: 'column',yAxis: 1";
                        }
                        else
                        {
                            strSerial += ",type: 'spline'";
                        }
                        strSerial += ",data:";
                        strSerial += dr["SERIESDATA"].ToString();


                        strSerial += ",stack:'type'";


                        strSerial += "}";
                        if (iRow != ds.Tables[0].Rows.Count - 1)
                        {
                            strSerial += ",";
                        }
                        //chartser.seriesdata = dr["SERIESDATA"].ToString();
                    }
                    strSerial += "]";
                }
                else
                {
                    strSerial = "[]";
                }
                chartser.seriesdata = strSerial;
                return chartser;
            }
            else
            {
                string sql = "select USERNAME,SERIESNAME,SERIESDATA from Tbt_ChartSeries "
                    + "where CHARTNO = '" + chartno + "' ";
                sqldb db = new sqldb();
                DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
                ChartSeries chartser = new ChartSeries();
                chartser.username = "admin";
                chartser.seriesname = "test";
                string strSerial = "";
                if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    strSerial = "[";
                    for (int iRow = 0; iRow < ds.Tables[0].Rows.Count; iRow++)
                    {
                        strSerial += "{ name:";
                        DataRow dr = ds.Tables[0].Rows[iRow];
                        strSerial += "'" + dr["SERIESNAME"].ToString() + "'";
                        strSerial += ",data:";
                        strSerial += dr["SERIESDATA"].ToString();

                        if (ds.Tables[0].Rows.Count == 4)
                        {
                            if (iRow <= 1)
                            {
                                strSerial += ",stack:'type'";
                            }
                            else
                            {
                                strSerial += ",stack:'money'";
                            }
                        }
                        else
                        {
                            strSerial += ",stack:'type'";
                        }
                        strSerial += "}";
                        if (iRow != ds.Tables[0].Rows.Count - 1)
                        {
                            strSerial += ",";
                        }
                        //chartser.seriesdata = dr["SERIESDATA"].ToString();
                    }
                    strSerial += "]";
                }
                else
                {
                    strSerial = "[]";
                }
                chartser.seriesdata = strSerial;
                return chartser;
            }



            //strJYPeople = "[{ name: '上海', data: [0,0],stack: 'type' },{ name: '深圳', data: [0,0],stack: 'type' }]";
        }

        public ChartSeries getsecChartSeries(string chartno, string selvalue, string uname)
        {
            StringBuilder sbsql = new StringBuilder();
            if ((selvalue == "") && (selvalue != "1"))
                sbsql.Append(" 1=1 ");



            string[] ItemList = selvalue.Split(',');

            if ((ItemList.Length > 0) && (selvalue != "1"))
            {
                for (int i = 0; i < ItemList.Length; i++)
                {
                    if (i == 0)
                        sbsql.Append("'" + ItemList[i] + "'");
                    else
                        sbsql.AppendLine(",'" + ItemList[i] + "'");
                }

                sbsql.Append(" and SELECTVALUE  IN (" + sbsql.ToString() + ")");


            }
            else
            {
                sbsql.Append("  and  1=1 ");
            }



            string sql = "select USERNAME,SERIESNAME,SERIESDATA from Tbt_ChartSeries "
                + "where CHARTNO = '" + chartno + "' and USERNAME = '" + uname + "'";
            if (sbsql.ToString() != "")
            {
                sql += sbsql.ToString();
            }

            sqldb db = new sqldb();
            DataSet ds = db.ExecuteDataset(sqlconn.GetConnStr(), CommandType.Text, sql);
            ChartSeries chartser = new ChartSeries();
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                DataRow dr2 = ds.Tables[0].Rows[1];
                chartser.username = dr["USERNAME"].ToString();
                chartser.seriesname = dr["SERIESNAME"].ToString();
                chartser.seriesdata = dr["SERIESDATA"].ToString();
                chartser.secseriesdata = dr2["SERIESDATA"].ToString();

            }
            return chartser;
        }





    }

}
